<?php
/**
 * Extract EPF DB -> Buil a house for a specified zip_code
 */

use Fox\Dao\SqlFragment;
use Fox\Common\Localization\SubRegion;
use Fox\Common\Localization\City;
use Fox\Common\Localization\City\Street;
use Fox\Html\CheckBox;
use Fox\Html\Input;
use Fox\Html\Screen;

set_time_limit(Xview::TIMEOUT_VERY_LONG);

$screen = new Screen("extract","extract");
$block = new Block("b","Extract Phones");
$block->add(new TextArea("postal_code",true));
$block->add(new CheckBox("commit"));
$block->add(new Input("dbcnt",true,1));
$screen->add($block);

if($screen->posted())
{
	$inputs = $screen->get();
	if(!$screen->errors())
	{
		if(!empty($inputs['postal_code']))
		{
			$sql = null;
			$zipcodes = preg_split("/,/", $inputs['postal_code']);
			foreach($zipcodes as $zipcode)
			{
				if(empty($sql))
					$sql = " p.postal_code LIKE '$zipcode%' ";
				else
					$sql .= "OR p.postal_code LIKE '$zipcode%' ";
			}
			$fragments[] = new Fox\Dao\SqlFragment($sql);
		}
		$query = Fox\Dao::infos(City::className())->handle()->createQueryBuilder();
		$query->select('p.cityname','p.adress','p.number','s.cityid','s.streetid','count(*) as code')
				->from('phone_cutover','p')
				->innerJoin('p','cities','c','c.postal_code = p.postal_code AND c.fullname = p.cityname')
				->leftJoin('p','city_streets','s','s.cityid = c.cityid AND p.adress = s.fullname')
				->groupBy('p.cityname','p.adress','p.number','s.cityid','s.streetid')
				->having('code = 1');
		$query->setMaxResults($inputs['dbcnt']);		
		$fragments[] = new SqlFragment("p.status = ?",array(0));
		$fragments[] = new SqlFragment("s.streetid IS NOT NULL");
		$fragment = SqlFragment::combine($fragments);
		$result = $fragment->pushIn($query)->execute()->fetchAll(PDO::FETCH_ASSOC);
		$i = 0;
		push_msg("I","Nb lines loaded :".count($result));
		
		foreach($result as $detail)
		{
			if($i == 0)
			{
			//	$handle = Fox\Dao::infos(City::className())->handle();
			//	$handle->beginTransaction();
			}
			// Prepare Update
			$fields['cityid'] = $detail['cityid'];
			$fields['streetid'] = $detail['streetid'];
			$fields['status'] = 2;
			$where['cityname'] = $detail['cityname'];
			$where['adress'] = $detail['adress'];
			$where['number'] = $detail['number'];
			// Update Original Source
			echo "UPDATE  `orinea_fr_address`.`phone_cutover` SET `cityid` =  '{$detail['cityid']}',`streetid` =  '{$detail['streetid']}',`status` =  2 ";
			echo "WHERE cityname = '{$detail['cityname']}' AND adress = '{$detail['adress']}' AND number = '{$detail['number']}'<br>";
			//$handle->update('phone_cutover',$fields,$where);
			$i++;
			if($i == 500)
			{
				echo "start ";
			//	$handle->commit();
				echo "stop ";
				$i = 0;
			}
		}
		if(isset($handle) && $i > 0 )
			$handle->commit();
	}
}
echo $screen->display();